USE [STATION]
GO
/****** Object:  StoredProcedure [dbo].[sp_Nurse_GetPatient_LabResult_NormalValues_Tabular1]    Script Date: 8/6/2018 5:15:04 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[sp_Nurse_GetPatient_LabResult_NormalValues_Tabular1]
--declare
	@Hospnum as Varchar(10),
	@StartDate as varchar(20),
	@EndDate as varchar(20),
	@Itemid as varchar(20) = ''
AS

--set @Hospnum = '7623'

if @ItemId = ''
		Begin
		select (convert(datetime, (convert(varchar(10), a.VerifyDate, 101))) + ' ' + convert(varchar(5), a.VerifyDate, 114)), a.Hospnum, a.Idnum, a.Requestnum,
			case 
				when a.labexamID IN (Select LabExamID from Build_File..tbCoLabExam where Form='P') then c.ItemID
				else a.labexamID
			end as labexamid,
			a.labsectionid ,
			--case a.labexamid
			--	when '1' then d.labsection + ' (CBC)'
			--	else 
			d.labsection
			  as section ,
			case 
				when a.labexamid = '1' then ltrim(convert(varchar(30),e.shortname) + ' ' + convert(varchar(35),e.resultname))
				when a.labexamID IN (Select LabExamID from Build_File..tbCoLabExam where Form='P' and labexamID <> '1') then g.labexam
				else b.labexam
				end as Exam ,
			case e.formtype
				when '1' then isnull(c.StrNValues,'') -- convert(varchar(10),cast(c.minvalue as decimal(10,2))) + ' - ' +	convert(varchar(10),cast(c.maxvalue as decimal(10,2))) + ' ' + c.unit 
				when '7' then isnull(c.StrNValues,'') + ' ' +  isnull(c.Unit,'')
				when '0' then isnull(c.StrNValues,'') + ' '  + isnull(c.Unit,'')
				when 'U' then isnull(c.StrNValues,'')+ ' '  + isnull(c.Unit,'')
				when '8' then isnull(c.ConvStrNValues,c.StrNValues) + ' ' + isnull(c.ConvUnit,'')
				when 'B' then isnull(c.ConvStrNValues,c.StrNValues) + ' ' + isnull(c.ConvUnit,'')
				when 'P' then isnull(c.StrNValues,'') + ' ' +isnull(c.Unit,'')
				else 'No Normal Value'
				END
				as NormalValue,
			case e.formtype
				when '1' then isnull(c.Strresult,'') --convert(varchar(10),cast(c.result as decimal(10,2)))
				when '7' then isnull(c.Strresult,'')
				when '0' then isnull(c.Strresult,'')
				when 'U' then isnull(c.Strresult,c.result)
				when 'P' then isnull(c.Strresult,c.result)
				when '8' then 
						case when isnull(c.ConvStrResult,'') = ''
						then 
						 isnull(c.Strresult,c.result)
						else
						 isnull(c.ConvStrResult,'')
						 end
	
				when 'B' then 
						case when isnull(c.ConvStrResult,'') = ''
						then 
						 isnull(c.Strresult,c.result)
						else
						 isnull(c.ConvStrResult,'')
						 end
				else 'No Result' 
				END
				 as Result ,
			--convert(varchar(10),Isnull(a.Verifydate,a.ResultDate),101) + substring(convert(varchar(19),Isnull(a.Verifydate,a.ResultDate),100),12,8) as ResultDate,
			Isnull(a.Verifydate,a.ResultDate) as ResultDate,
			e.formtype as FormType, a.VerifyDate, a.RefNum, convert(varchar(10), a.ResultDate, 101) As Result_Date,
			  c.SortOrder
			,
			 -- 	 Case when a.FormType='P'
			 --then c.SortOrder
			 --else
			 -- '1'
			 -- end as SortOrder,
			case when a.FormType='P' then b.Labexam
			else '' end as ProfileName
		from laboratory..tblablogbook a
		left outer join build_file..tbcolabsection d
			on a.labsectionid = d.labsectionid
		left outer join build_file..tbcolabexam b
			on a.labexamid = b.labexamid
		left outer join laboratory..tblabresultnvalues c
			on a.requestnum = c.requestnum --and a.labexamid = c.itemid
		left outer join build_file..tbcolabvalues e 
			on e.code = c.itemid
		left outer join build_file..tbcolabexam g
			on c.Itemid = g.labexamid
		where a.Hospnum = @Hospnum and a.labsectionid = 'B'
		--and (isnumeric(c.Strresult) = 1 )	
		and (convert(datetime, (convert(varchar(10), a.VerifyDate, 101))) + ' ' + convert(varchar(5), a.VerifyDate, 114)) in 
		(
		select distinct top 20 convert(datetime, (convert(varchar(10), VerifyDate, 101))) + ' ' + convert(varchar(5), VerifyDate, 114)
		from laboratory..tbLabLogbook
		where hospnum = @Hospnum and labsectionid = 'B'
			  and Verifydate between @StartDate and @EndDate + ' 23:59:59.99'
		order by convert(datetime, (convert(varchar(10), VerifyDate, 101))) + ' ' + convert(varchar(5), VerifyDate, 114) desc
		)

		UNION ALL

		select (convert(datetime, (convert(varchar(10), a.VerifyDate, 101))) + ' ' + convert(varchar(5), a.VerifyDate, 114)), a.Hospnum, a.Idnum, a.Requestnum,
			case 
				when a.labexamID IN (Select LabExamID from Build_File..tbCoLabExam where Form='P') then c.ItemID
				else a.labexamID
			end as labexamid,
			a.labsectionid ,
			--case a.labexamid
			--	when '1' then d.labsection + ' (CBC)'
			--	else
				 d.labsection as section ,
			case 
				when a.labexamid = '1' then ltrim(convert(varchar(30),e.shortname) + ' ' + convert(varchar(35),e.resultname))
				when a.labexamID IN (Select LabExamID from Build_File..tbCoLabExam where Form='P' and labexamID <> '1') then g.labexam
				else b.labexam
				end as Exam ,
			case e.formtype
				--when '1' then convert(varchar(10),cast(c.minvalue as decimal(10,2))) + ' - ' +	convert(varchar(10),cast(c.maxvalue as decimal(10,2))) + ' ' + c.unit 
				when '1' then isnull(c.StrNValues,'') + ' ' + isnull(c.Unit,'')
				when '7' then isnull(c.StrNValues,'') + ' '  + isnull(c.Unit,'')
				when '0' then isnull(c.StrNValues,'') + ' ' + isnull(c.Unit,'')
				when 'U' then isnull(c.StrNValues,'') + ' '  + isnull(c.Unit,'')
				when '8' then isnull(c.ConvStrNValues,c.StrNValues) + ' ' +  isnull(c.ConvUnit,'')
				when 'B' then isnull(c.ConvStrNValues,c.StrNValues) + ' ' + isnull(c.ConvUnit,'')
				when 'P' then isnull(c.StrNValues,'') + ' ' + isnull(c.Unit,'')
				else 'No Normal Value'
				END
				as NormalValue,
			case e.formtype
				when '1' then isnull(c.Strresult,'')
				when '7' then isnull(c.Strresult,'')
				when '0' then isnull(c.Strresult,'')
				when 'U' then isnull(c.Strresult,'')
				when '8' then 
						case when isnull(c.ConvStrResult,'') = ''
						then 
						 isnull(c.Strresult,c.result)
						else
						 isnull(c.ConvStrResult,'')
						 end
				when 'P' then isnull(c.Strresult,c.StrResult)
				when 'B' then isnull(c.ConvStrResult,c.StrResult)
				else 'No Result' 
				END
				 as Result ,
			--convert(varchar(10),Isnull(a.Verifydate,a.ResultDate),101) + substring(convert(varchar(19),Isnull(a.Verifydate,a.ResultDate),100),12,8) as ResultDate,
			Isnull(a.Verifydate,a.ResultDate) as ResultDate,
			e.formtype as FormType, a.VerifyDate, a.RefNum, convert(varchar(10), a.ResultDate, 101) As Result_Date,
			 c.SortOrder,
			 case when a.FormType='P' then b.Labexam
			else '' end as ProfileName
		from laboratory..tblablogbook a
		left outer join build_file..tbcolabsection d
			on a.labsectionid = d.labsectionid
		left outer join build_file..tbcolabexam b
			on a.labexamid = b.labexamid
		left outer join laboratory..tblabresultnvalues c
			on a.requestnum = c.requestnum --and a.labexamid = c.itemid
		left outer join build_file..tbcolabvalues e 
			on e.code = c.itemid
		left outer join build_file..tbcolabexam g
			on c.Itemid = g.labexamid
		where a.Hospnum = @Hospnum and a.labsectionid = 'A'
			  and Verifydate between @StartDate and @EndDate + ' 23:59:59.99'
		--and (isnumeric(c.Strresult) = 1 )	
		and (convert(datetime, (convert(varchar(10), a.VerifyDate, 101))) + ' ' + convert(varchar(5), a.VerifyDate, 114)) in 
		(
		select distinct top 20 convert(datetime, (convert(varchar(10), VerifyDate, 101))) + ' ' + convert(varchar(5), VerifyDate, 114)
		from laboratory..tbLabLogbook
		where hospnum = @Hospnum and labsectionid = 'A'
		order by convert(datetime, (convert(varchar(10), verifydate, 101))) + ' ' + convert(varchar(5), verifydate, 114) desc
		)
		order by section,SortOrder asc,NormalValue, Isnull(a.Verifydate,a.ResultDate);

end

--=========================================================================
--++++++======== PER ITEMID
--=================================================================================

Else
	Begin
		select (convert(datetime, (convert(varchar(10), a.VerifyDate, 101))) + ' ' + convert(varchar(5), a.VerifyDate, 114)), a.Hospnum, a.Idnum, a.Requestnum,
			case 
				when a.labexamID IN (Select LabExamID from Build_File..tbCoLabExam where Form='P') then c.ItemID
				else a.labexamID
			end as labexamid,
			a.labsectionid ,
			--case a.labexamid
			--	when '1' then d.labsection + ' (CBC)'
			--	else 
			d.labsection
			  as section ,
			case 
				when a.labexamid = '1' then ltrim(convert(varchar(30),e.shortname) + ' ' + convert(varchar(35),e.resultname))
				when a.labexamID IN (Select LabExamID from Build_File..tbCoLabExam where Form='P' and labexamID <> '1') then g.labexam
				else b.labexam
				end as Exam ,
			case e.formtype
				when '1' then isnull(c.StrNValues,'') -- convert(varchar(10),cast(c.minvalue as decimal(10,2))) + ' - ' +	convert(varchar(10),cast(c.maxvalue as decimal(10,2))) + ' ' + c.unit 
				when '7' then isnull(c.StrNValues,'') + ' ' +  isnull(c.Unit,'')
				when '0' then isnull(c.StrNValues,'') + ' '  + isnull(c.Unit,'')
				when 'U' then isnull(c.StrNValues,'')+ ' '  + isnull(c.Unit,'')
				when '8' then isnull(c.ConvStrNValues,c.StrNValues) + ' ' + isnull(c.ConvUnit,'')
				when 'B' then isnull(c.ConvStrNValues,c.StrNValues) + ' ' + isnull(c.ConvUnit,'')
				when 'P' then isnull(c.StrNValues,'') + ' ' +isnull(c.Unit,'')
				else 'No Normal Value'
				END
				as NormalValue,
			case e.formtype
				when '1' then isnull(c.Strresult,'') --convert(varchar(10),cast(c.result as decimal(10,2)))
				when '7' then isnull(c.Strresult,'')
				when '0' then isnull(c.Strresult,'')
				when 'U' then isnull(c.Strresult,c.result)
				when 'P' then isnull(c.Strresult,c.result)
				when '8' then 
						case when isnull(c.ConvStrResult,'') = ''
						then 
						 isnull(c.Strresult,c.result)
						else
						 isnull(c.ConvStrResult,'')
						 end
	
				when 'B' then 
						case when isnull(c.ConvStrResult,'') = ''
						then 
						 isnull(c.Strresult,c.result)
						else
						 isnull(c.ConvStrResult,'')
						 end
				else 'No Result' 
				END
				 as Result ,
			--convert(varchar(10),Isnull(a.Verifydate,a.ResultDate),101) + substring(convert(varchar(19),Isnull(a.Verifydate,a.ResultDate),100),12,8) as ResultDate,
			Isnull(a.Verifydate,a.ResultDate) as ResultDate,
			e.formtype as FormType, a.VerifyDate, a.RefNum, convert(varchar(10), a.ResultDate, 101) As Result_Date,
			  c.SortOrder
			,
			 -- 	 Case when a.FormType='P'
			 --then c.SortOrder
			 --else
			 -- '1'
			 -- end as SortOrder,
			case when a.FormType='P' then b.Labexam
			else '' end as ProfileName
		from laboratory..tblablogbook a
		left outer join build_file..tbcolabsection d
			on a.labsectionid = d.labsectionid
		left outer join build_file..tbcolabexam b
			on a.labexamid = b.labexamid
		left outer join laboratory..tblabresultnvalues c
			on a.requestnum = c.requestnum --and a.labexamid = c.itemid
		left outer join build_file..tbcolabvalues e 
			on e.code = c.itemid
		left outer join build_file..tbcolabexam g
			on c.Itemid = g.labexamid
		where a.Hospnum = @Hospnum and a.labsectionid = 'B'
		--and (isnumeric(c.Strresult) = 1 )	
		and a.LabExamId= @Itemid
		and (convert(datetime, (convert(varchar(10), a.VerifyDate, 101))) + ' ' + convert(varchar(5), a.VerifyDate, 114)) in 
		(
		select distinct top 20 convert(datetime, (convert(varchar(10), VerifyDate, 101))) + ' ' + convert(varchar(5), VerifyDate, 114)
		from laboratory..tbLabLogbook
		where hospnum = @Hospnum and labsectionid = 'B'
			  and Verifydate between @StartDate and @EndDate + ' 23:59:59.99'
		order by convert(datetime, (convert(varchar(10), VerifyDate, 101))) + ' ' + convert(varchar(5), VerifyDate, 114) desc
		)

		UNION ALL

		select (convert(datetime, (convert(varchar(10), a.VerifyDate, 101))) + ' ' + convert(varchar(5), a.VerifyDate, 114)), a.Hospnum, a.Idnum, a.Requestnum,
			case 
				when a.labexamID IN (Select LabExamID from Build_File..tbCoLabExam where Form='P') then c.ItemID
				else a.labexamID
			end as labexamid,
			a.labsectionid ,
			--case a.labexamid
			--	when '1' then d.labsection + ' (CBC)'
			--	else
				 d.labsection as section ,
			case 
				when a.labexamid = '1' then ltrim(convert(varchar(30),e.shortname) + ' ' + convert(varchar(35),e.resultname))
				when a.labexamID IN (Select LabExamID from Build_File..tbCoLabExam where Form='P' and labexamID <> '1') then g.labexam
				else b.labexam
				end as Exam ,
			case e.formtype
				--when '1' then convert(varchar(10),cast(c.minvalue as decimal(10,2))) + ' - ' +	convert(varchar(10),cast(c.maxvalue as decimal(10,2))) + ' ' + c.unit 
				when '1' then isnull(c.StrNValues,'') + ' ' + isnull(c.Unit,'')
				when '7' then isnull(c.StrNValues,'') + ' '  + isnull(c.Unit,'')
				when '0' then isnull(c.StrNValues,'') + ' ' + isnull(c.Unit,'')
				when 'U' then isnull(c.StrNValues,'') + ' '  + isnull(c.Unit,'')
				when '8' then isnull(c.ConvStrNValues,c.StrNValues) + ' ' +  isnull(c.ConvUnit,'')
				when 'B' then isnull(c.ConvStrNValues,c.StrNValues) + ' ' + isnull(c.ConvUnit,'')
				when 'P' then isnull(c.StrNValues,'') + ' ' + isnull(c.Unit,'')
				else 'No Normal Value'
				END
				as NormalValue,
			case e.formtype
				when '1' then isnull(c.Strresult,'')
				when '7' then isnull(c.Strresult,'')
				when '0' then isnull(c.Strresult,'')
				when 'U' then isnull(c.Strresult,'')
				when '8' then 
						case when isnull(c.ConvStrResult,'') = ''
						then 
						 isnull(c.Strresult,c.result)
						else
						 isnull(c.ConvStrResult,'')
						 end
				when 'P' then isnull(c.Strresult,c.StrResult)
				when 'B' then isnull(c.ConvStrResult,c.StrResult)
				else 'No Result' 
				END
				 as Result ,
			--convert(varchar(10),Isnull(a.Verifydate,a.ResultDate),101) + substring(convert(varchar(19),Isnull(a.Verifydate,a.ResultDate),100),12,8) as ResultDate,
			Isnull(a.Verifydate,a.ResultDate) as ResultDate,
			e.formtype as FormType, a.VerifyDate, a.RefNum, convert(varchar(10), a.ResultDate, 101) As Result_Date,
			 c.SortOrder,
			 case when a.FormType='P' then b.Labexam
			else '' end as ProfileName
		from laboratory..tblablogbook a
		left outer join build_file..tbcolabsection d
			on a.labsectionid = d.labsectionid
		left outer join build_file..tbcolabexam b
			on a.labexamid = b.labexamid
		left outer join laboratory..tblabresultnvalues c
			on a.requestnum = c.requestnum --and a.labexamid = c.itemid
		left outer join build_file..tbcolabvalues e 
			on e.code = c.itemid
		left outer join build_file..tbcolabexam g
			on c.Itemid = g.labexamid
		where a.Hospnum = @Hospnum and a.labsectionid = 'A'
			  and Verifydate between @StartDate and @EndDate + ' 23:59:59.99'
		--and (isnumeric(c.Strresult) = 1 )	
		and a.Labexamid=@Itemid
		and (convert(datetime, (convert(varchar(10), a.VerifyDate, 101))) + ' ' + convert(varchar(5), a.VerifyDate, 114)) in 
		(
		select distinct top 20 convert(datetime, (convert(varchar(10), VerifyDate, 101))) + ' ' + convert(varchar(5), VerifyDate, 114)
		from laboratory..tbLabLogbook
		where hospnum = @Hospnum and labsectionid = 'A'
		order by convert(datetime, (convert(varchar(10), VerifyDate, 101))) + ' ' + convert(varchar(5), VerifyDate, 114) desc
		)
		order by section,SortOrder asc,NormalValue, Isnull(a.Verifydate,a.ResultDate);


End